/**
 * 全局性的数据库操作框架
 *
 * @author 迟胜强
 * @QQ 137428857
 * @date 2015-08-23
 * @description 这是一个数据库操作框架,主要针对不同类型的数据库操作
 * @level 5
 * @from 此代码来自Aorey Summer Framework 通过传递不同的数据库连接池区分操作类型
 *
 */
package aorey.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.JSONArray;

public class DataBaseFactory
{
    // 数据库连接对象
    protected Connection _conn = null;
    // 数据库操作对象
    protected Statement _stat = null;
    protected String _error_msg = null;
    // 数据库记录及对象
    protected ResultSet _rs = null;
    protected String _json_str = null;
    protected int _size = 0;
    protected ResultSetMetaData _rsmd = null;

    protected List<?> fieldList = null;

    /**
     * 设置数据库连接池对象,之后将针对不同的连接通道操作
     *
     * @param conn 数据库连接对象
     * @return 
     */
    public boolean getConnection(Connection conn)
    {

        try
        {
            if (!conn.isClosed())
            {
                // Statement statement = this._conn.createStatement();
                // this._stat = statement;

                this._conn = conn;
                return true;
            } else
            {
                return false;
            }
        } catch (Exception e)
        {
            this._error_msg = e.getMessage();

            return false;
        }
    }

    /**
     * 执行没有结果的SQL语句
     *
     * @param sql 数据库执行语句
     * @return 
     */
    public int doSQL(String sql)
    {
        try
        {
            this._conn.setAutoCommit(true);
            this._stat = this._conn.createStatement();
            int i = this._stat.executeUpdate(sql);
            this._stat.close();
            this._stat = null;
            return i;
        } catch (SQLException e)
        {
            this._error_msg = e.getMessage();
            return -1;
        }
    }

    /**
     * 执行有记录集的SQL语句
     *
     * @param sql 查询语句
     * @return json格式结果
     */
    public String getResult(String sql)
    {
        StringBuilder tmp = new StringBuilder();
        JSONArray resultData = new JSONArray();
        try
        {
            this._conn.setAutoCommit(false);
            this._stat = this._conn.createStatement();
            this._rs = this._stat.executeQuery(sql);
            this._rsmd = this._rs.getMetaData();
            int L = this._rsmd.getColumnCount();
            ArrayList<String> field = new ArrayList<>();
            for (int i = 1; i <= L; i++)
            {
                field.add(this._rsmd.getColumnLabel(i));
            }
            int field_size = field.size();
            this.fieldList = field;
            while (this._rs.next())
            {
                JSONObject dataField = new JSONObject();
                for (int j = 0; j < field_size; j++)
                {
                    dataField.put(field.get(j), this._rs.getString(field.get(j)));
                }
                resultData.add(dataField);
            }
            this._json_str = resultData.toJSONString();
            this._rs.close();
            this._stat.close();
            return this._json_str;
        } catch (Exception e)
        {
            this._error_msg = e.getMessage().toString();
            return "";
        }

    }

    /**
     * 获取JSON格式的数据,为了兼容之前的数据格式
     *
     * @param sql
     * @return
     */
    public JSONArray getJsonResult(String sql)
    {
        StringBuffer tmp = new StringBuffer();
        JSONArray resultData = new JSONArray();
        try
        {
            this._conn.setAutoCommit(false);
            this._stat = this._conn.createStatement();
            this._rs = this._stat.executeQuery(sql);
            this._rsmd = this._rs.getMetaData();
            int L = this._rsmd.getColumnCount();
            ArrayList<String> field = new ArrayList<String>();
            for (int i = 1; i <= L; i++)
            {
                field.add(this._rsmd.getColumnLabel(i));
            }
            int field_size = field.size();
            this.fieldList = field;
            while (this._rs.next())
            {
                JSONObject dataField = new JSONObject();
                for (int j = 0; j < field_size; j++)
                {
                    dataField.put(field.get(j), this._rs.getString(field.get(j)));
                }
                resultData.add(dataField);
            }
            this._rs.close();
            this._stat.close();
        } catch (Exception e)
        {
            this._error_msg = e.getMessage();
            return null;
        }

        return resultData;
    }

    /**
     * 获取字段数量
     * @return 
     */
    public int getFieldSize()
    {
        if (this.fieldList != null)
        {
            return this.fieldList.size();

        } else
        {
            return 0;
        }

    }

    /**
     * 获取字段名
     * @param i
     * @return 
     */
    public String getFieldName(int i)
    {
        if (this.fieldList != null)
        {
            try
            {
                return this.fieldList.get(i).toString();
            } catch (Exception e)
            {
                return "";
            }
        } else
        {
            return "";
        }

    }

    /**
     * 关闭数据库连接
     *
     * @info 实际测试中好像不能这么做,否者会出现问题,好像是连接池的问题
     */
    public void close()
    {

        try
        {
            this._conn.close();
        } catch (SQLException e)
        {
            this.setError(e.getMessage());
        }
    }

    /**
     * 获取记录集中的数据
     *
     * @param row 记录集行
     * @param field 记录集字段名
     * @return 数据库结果
     */
    public String getValue(int row, String field)
    {
        if (this._json_str != null)
        {
            try
            {
                JSONArray jsonArray = JSONArray.parseArray(this._json_str);
                JSONObject jsonObject = jsonArray.getJSONObject(row);
                return jsonObject.get(field).toString();
            } catch (Exception e)
            {
                this._error_msg = e.getMessage();
                return "";
            }
        } else
        {
            return "";
        }
    }

    /**
     * 获取记录集总数
     *
     * @return 记录集总数
     */
    public int getSize()
    {
        if (this._json_str != null)
        {
            try
            {
                JSONArray jsonArray = JSONArray.parseArray(this._json_str);
                return jsonArray.size();
            } catch (Exception e)
            {
                return 0;
            }
        } else
        {
            return 0;
        }
    }

    /**
     * 释放记录集资源,以备下次使用
     */
    public void free()
    {
        this._rs = null;
        this._stat = null;
        this._rsmd = null;
    }

    /**
     * 获取错误
     *
     * @return
     */
    public String getError()
    {
        return this._error_msg;
    }

    public boolean getState()
    {

        try
        {
            return !this._conn.isClosed();
        } catch (SQLException e)
        {
            
            this.setError(e.getMessage());
            return false;
        }
    }

    /**
     * 获取json结果中的数据总数
     *
     * @param json
     * @return
     */
    public int getResultSize(String json)
    {
        JSONArray jsonArray = JSONArray.parseArray(json);
        return jsonArray.size();
    }

    public String getResultValue(String json, int row, String field)
    {
        JSONArray jsonArray = JSONArray.parseArray(json);
        JSONObject jsonObject = JSONObject.parseObject(jsonArray.get(row).toString());
        return jsonObject.getString(field);
    }

    /**
     * 设置错误
     * @param error
     */
    public void setError(String error)
    {

        this._error_msg = error;
    }
}
